<?php
class Activities extends Model{
	
	function getAll(){
		$sql ="SELECT *,c.pic as cust,at.pic,at.id,c.address,c.email,c.tlp,MONTHNAME(activity_date) as month_name,YEAR(activity_date) as year,WEEK(activity_date) as week FROM activity_transaction at ";
		$sql.="LEFT JOIN activity a ON a.id=at.activity_id ";
        $sql.="LEFT JOIN customers c ON c.id=at.customer_id ";
        $sql.="LEFT JOIN users u ON u.username=at.pic ";
        $sql.="WHERE at.activity_date='".date("Y-m-d")."'";
		//$sql.=" AND at.pic='".$this->session->userdata('user_id')."'";
		if(($this->session->userdata('user_pos')==$this->config->item('ta'))):
			$sql.=" AND c.rev='".$this->session->userdata('user_id')."'";
        elseif(($this->session->userdata('user_pos')==$this->config->item('se'))):
			$sql.=" AND c.rev2='".$this->session->userdata('user_id')."' AND activity_id<>2 AND activity_id<>4 ";	
		elseif(($this->session->userdata('user_pos')==$this->config->item('ae'))):	
			$sql.=" AND c.rev3='".$this->session->userdata('user_id')."'";
        endif;
        /*if(($this->session->userdata('user_pos')== $this->config->item('ta') ) ):
            $sql.=" AND at.pic='".$this->session->userdata('user_id')."'";
        elseif(($this->session->userdata('user_pos')== $this->config->item('se')) && ($this->session->userdata('user_group')== $this->config->item('user')) ) :    
            $sql.=" AND at.pic='".$this->session->userdata('user_ta')."'";
        elseif(($this->session->userdata('user_pos')== $this->config->item('se')) && ($this->session->userdata('user_group')== $this->config->item('user_dna')) ) :    
            $sql.=" AND at.pic='".$this->session->userdata('user_id')."'";    
        elseif(($this->session->userdata('user_group')== $this->config->item('admin')) ):    
            $sql.= " AND u.group_id=".$this->config->item('user'); 
        elseif(($this->session->userdata('user_group')== $this->config->item('admin_dna')) ):    
            $sql.= " AND u.group_id=".$this->config->item('user_dna'); 
        endif;*/
        $sql.=" ORDER BY at.activity_date DESC,at.activity_time DESC ";
		$query=$this->db->query($sql);
		return $query->result_array();
	}
    
    function getProducts(){
		$sql ="SELECT * FROM products  ";
		$query=$this->db->query($sql);
		return $query->result_array();
	}
    
    function getActivityWeek($week,$year,$user='',$group=''){
        $sql = " SELECT *, ";
        $sql.= " (SELECT COUNT(activity_id) ";
        $sql.= " FROM activity_transaction at ";
        $sql.= " LEFT JOIN users u ON u.username=at.pic ";
        $sql.= " WHERE WEEK(at.activity_date)='".$week."'";
        $sql.=" AND YEAR(at.activity_date)='".$year."'";
        $sql.=" AND at.activity_id=1";
        if($user<>""):
            $sql.=" AND at.pic='".$user."'";
        endif;
        if($group<>""):
            $sql.=" AND u.group_id='".$group."'";
        endif;
        $sql.= ")";
        $sql.= " AS call1 ,";
        $sql.= " (SELECT COUNT(activity_id) ";
        $sql.= " FROM activity_transaction at ";
        $sql.= " LEFT JOIN users u ON u.username=at.pic ";
        $sql.= " WHERE WEEK(at.activity_date)='".$week."'";
        $sql.=" AND YEAR(at.activity_date)='".$year."'";
        $sql.=" AND at.activity_id=2";
        if($user<>""):
            $sql.=" AND at.pic='".$user."'";
        endif;
        if($group<>""):
            $sql.=" AND u.group_id='".$group."'";
        endif;
        $sql.= ")";
        $sql.= " AS call_result, ";
        
        $sql.= " (SELECT COUNT(activity_id) ";
        $sql.= " FROM activity_transaction at ";
        $sql.= " LEFT JOIN users u ON u.username=at.pic ";
        $sql.= " WHERE WEEK(at.activity_date)='".$week."'";
        $sql.="  AND YEAR(at.activity_date)='".$year."'";
        $sql.="  AND at.activity_id=3";
        if($user<>""):
            $sql.=" AND at.pic='".$user."'";
        endif;
        if($group<>""):
            $sql.=" AND u.group_id='".$group."'";
        endif;
        $sql.= ")";
        $sql.= " AS presentation, ";
        
        $sql.= " (SELECT COUNT(activity_id) ";
        $sql.= " FROM activity_transaction at ";
        $sql.= " LEFT JOIN users u ON u.username=at.pic ";
        $sql.= " WHERE  ";
        $sql.= "  WEEK(at.activity_date)='".$week."'";
        $sql.=" AND YEAR(at.activity_date)='".$year."'";
        $sql.=" AND at.activity_id=4";
        if($user<>""):
            $sql.=" AND at.pic='".$user."'";
        endif;
        if($group<>""):
            $sql.=" AND u.group_id='".$group."'";
        endif;
        $sql.= ")";
        $sql.= " AS meeting, ";
        
        $sql.= " (SELECT COUNT(activity_id) ";
        $sql.= " FROM activity_transaction at ";
        $sql.= " LEFT JOIN users u ON u.username=at.pic ";
        $sql.= " WHERE   ";
        $sql.= "  WEEK(at.activity_date)='".$week."'";
        $sql.=" AND YEAR(at.activity_date)='".$year."'";
        $sql.=" AND at.activity_id=5";
        if($user<>""):
            $sql.=" AND at.pic='".$user."'";
        endif;
        if($group<>""):
            $sql.=" AND u.group_id='".$group."'";
        endif;
        $sql.= ")";
        $sql.= " AS damage_claim ";
        
        $sql.= " FROM activity a ";
        $query=$this->db->query($sql);
		return $query->row_array();
    }
    
    function getSearch(){
		$sql ="SELECT *,at.pic,at.id,c.pic as cust,MONTHNAME(activity_date) as month_name,YEAR(activity_date) as year,WEEK(activity_date) as week FROM activity_transaction at ";
		$sql.="LEFT JOIN activity a ON a.id=at.activity_id ";
        $sql.="LEFT JOIN customers c ON c.id=at.customer_id ";
        
        if($this->input->post('search')):
        
            $sql.=" WHERE activity_date<>'0000-00-00'  ";
            
            if($this->input->post('datestart')<>""):
                $sql.="AND at.activity_date>='".$this->input->post('datestart')."' ";
            endif;
                
            if($this->input->post('datefinish')<>""):
                $sql.=" AND at.activity_date<='".$this->input->post('datefinish')."'  ";
            endif;
            
            
            if($this->input->post('type_search')<>""):
                 $sql.=" AND at.activity_id=".$this->input->post('type_search')."  ";
            endif;
            
            if($this->input->post('user_search')<>""):
                 $sql.=" AND at.pic='".$this->input->post('user_search')."' ";
            endif;
        
        else:
          $sql.="WHERE at.activity_date<='2010' ";
        endif;
		$query=$this->db->query($sql);
		return $query->result_array();
	}
    
    function getCustomer($user=''){
		$sql =" SELECT *,c.id FROM customers c ";
		$sql.=" LEFT JOIN customer_status s ON s.id=c.status_id ";
		$sql.=" WHERE c.spv_approve = 1  ";
        //if($this->session->userdata('user_group')== $this->config->item('user') ):
            if(($this->session->userdata('user_pos')==$this->config->item('ta'))):
                //$sql.=" AND c.rev='".$this->session->userdata('user_id')."'";
				$sql.=" AND c.rev='".$this->session->userdata('user_id')."'";
            elseif(($this->session->userdata('user_pos')==$this->config->item('se'))):
                //$sql.=" AND c.rev='".$this->session->userdata('user_ta')."'"; 
				$sql.=" AND c.rev2='".$this->session->userdata('user_id')."'";	
			elseif(($this->session->userdata('user_pos')==$this->config->item('ae'))):	
				$sql.=" AND c.rev3='".$this->session->userdata('user_id')."'";
            endif;
            /*if($user<>""):
                $sql.=" AND c.rev='".$user."'";
            endif;*/
        /*elseif($this->session->userdata('user_group')== $this->config->item('admin') ):
            $sql.=" AND c.type='Corporate' ";
        elseif(($this->session->userdata('user_pos')== $this->config->item('ta')) && ($this->session->userdata('user_group')== $this->config->item('user_dna')) ):        
            $sql.=" AND c.type='Corporate' ";    
        elseif(($this->session->userdata('user_pos')== $this->config->item('se')) && ($this->session->userdata('user_group')== $this->config->item('user_dna')) ):        
            $sql.=" AND c.type='Individual' ";
        elseif($this->session->userdata('user_group')== $this->config->item('admin_dna') ):
            $sql.=" AND c.rev2<>'' ";          
        endif;*/
        $query=$this->db->query($sql);
		return $query->result_array();
	}
    
    function getPeriode($type){
        $sql ="SELECT *,at.pic,at.id,c.pic as cust,c.address,c.email,c.tlp,MONTHNAME(activity_date) as month_name,YEAR(activity_date) as year,WEEK(activity_date) as week FROM activity_transaction at ";
		$sql.="LEFT JOIN activity a ON a.id=at.activity_id ";
        $sql.="LEFT JOIN customers c ON c.id=at.customer_id ";
        $sql.="LEFT JOIN users u ON u.username=at.pic ";
        if($type=='daily'){
        $sql.="WHERE at.activity_date='".date("Y-m-d")."'";
        } elseif($type=='weekly') {
        $sql.="WHERE WEEK(at.activity_date)='".date("W")."'";        
        
		/*
		}  elseif($type=='monthly') {
        $sql.="WHERE MONTH(at.activity_date)<='".date("m")."'";        
        }*/
		}  elseif($type=='monthly') {
        $sql.="WHERE MONTH(at.activity_date)='".date("m")."'";        
        }
		
  
        /*if(($this->session->userdata('user_pos')== $this->config->item('ta') ) ):
            $sql.=" AND at.pic='".$this->session->userdata('user_id')."'";
        elseif(($this->session->userdata('user_pos')==$this->config->item('se')) && $this->session->userdata('user_group')==$this->config->item('user') ):
            $sql.=" AND at.pic='".$this->session->userdata('user_ta')."'";
        elseif(($this->session->userdata('user_pos')==$this->config->item('se')) && $this->session->userdata('user_group')==$this->config->item('user_dna') ):
            $sql.=" AND at.pic='".$this->session->userdata('user_id')."'";    
        elseif(($this->session->userdata('user_group')== $this->config->item('admin')) ):    
            $sql.= " AND u.group_id=".$this->config->item('user');    
        elseif(($this->session->userdata('user_group')== $this->config->item('admin_dna')) ):    
            $sql.= " AND u.group_id=".$this->config->item('user_dna');     
        endif;*/
		if( ($this->session->userdata('user_pos')== $this->config->item('ta')) || ($this->session->userdata('user_pos')== $this->config->item('se')) || ($this->session->userdata('user_pos')== $this->config->item('ae')) ) 	
		$sql.=" AND at.pic='".$this->session->userdata('user_id')."'";
        
		$sql.=" ORDER BY at.activity_date DESC,at.activity_time DESC ";   
		$query=$this->db->query($sql);
		return $query->result_array();
	}
    
    function getType(){
		$sql ="SELECT * FROM activity WHERE id<>3 Order By id";
		$query=$this->db->query($sql);
		return $query->result_array();
	}
    
    function getCek(){
        $sql ="SELECT * FROM activity_transaction at ";
		$sql.="WHERE at.customer_id = '".$this->input->post('customer')."' AND activity_date = '".$this->input->post('date')."' LIMIT 1";
        $query=$this->db->query($sql);
		return $query->row_array();
    }
    
    function getData($id){
		$sql ="SELECT *,at.id,at.pic FROM activity_transaction at ";
		$sql.="LEFT JOIN activity a ON a.id=at.activity_id ";
        $sql.="LEFT JOIN customers c ON c.id=at.customer_id ";
        $sql.="LEFT JOIN customer_sector cs ON cs.id=c.sector_id ";
        $sql.="LEFT JOIN customer_group cg ON cg.id=c.group_id ";
        $sql.="LEFT JOIN customer_source cse ON cse.id=c.source_id ";
        $sql.="LEFT JOIN customer_status cst ON cst.id=c.status_id "; 
        $sql.="WHERE at.id = '".$id."' LIMIT 1";
		$query=$this->db->query($sql);
		return $query->row_array();
	}
    
    
    function getUsers(){
		$sql ="SELECT *,u.id FROM users u ";
		$sql.="LEFT JOIN user_group g ON g.id=u.group_id ";
		$sql.="LEFT JOIN user_position p ON p.id=u.pos_id ";
        $sql.= " WHERE u.pos_id =".$this->config->item('ta'); 
        $sql.= " AND u.sales=1 AND u.enabled=1 ";
        if( $this->session->userdata('user_group')== $this->config->item('admin') ):
            $sql.= " AND u.group_id=".$this->config->item('user');
		elseif( $this->session->userdata('user_group')== $this->config->item('admin_dna') ):
            $sql.= " AND u.group_id=".$this->config->item('user_dna');	
        endif;
		$query=$this->db->query($sql);
		return $query->result_array();
	}
    
    function getUserDNA(){
        $sql ="SELECT *,u.id FROM users u ";
		$sql.="LEFT JOIN user_group g ON g.id=u.group_id ";
		$sql.="LEFT JOIN user_position p ON p.id=u.pos_id ";
        $sql.= " WHERE u.pos_id =".$this->config->item('ta'); 
        $sql.= " AND u.sales=1 AND u.enabled=1 ";
        $sql.= " AND u.group_id=".$this->config->item('user_dna');
		$query=$this->db->query($sql);
		return $query->result_array();
    }
    
    function getSave(){
        $this->db->where('customer_name',$this->input->post('customer'));
        $Q   =$this->db->get('customers');
        $row =$Q->row_array();
        $cust= $row['id'];
        
        if($this->session->userdata('user_group')==$this->config->item('user')):
            $pic = $row['rev'];
        elseif($this->session->userdata('user_group')==$this->config->item('user_dna')):
            $pic = $this->session->userdata('user_id');
        elseif($this->session->userdata('user_group')==$this->config->item('admin_dna')):
            $pic = $this->input->post('user_dna');
        elseif(($this->session->userdata('user_group')==$this->config->item('user')) && ($this->session->userdata('user_pos')==$this->config->item('se')) ):
            $pic = $row['rev'];
        else:
            $pic = $row['rev'];        
        endif;
        
        $hour = $this->input->post('hour');
        $minute = $this->input->post('minute');
        $second = $this->input->post('second');
        
        if($this->session->userdata('user_group')==$this->config->item('admin')):
            $time = $hour.':'.$minute.':'.$second;
        else:
            $time = date('h:i:s');
        endif;    
        $value = array  (
                            'activity_desc'     =>  $this->input->post('desc'),
                            'activity_id'       =>  $this->input->post('type'),
                            'customer_id'       =>  $cust,
                            'pic'               =>  $pic,
                            'activity_time'     =>  $time,
                            'activity_date'     =>  $this->input->post('date'),
                            'activity_location' =>  $this->input->post('location'),
                            'activity_people'   =>  $this->input->post('people'),
                            'activity_objective'=>  $this->input->post('objective'),
                            'activity_result'   =>  $this->input->post('result'),
                            'activity_follow_up'=>  $this->input->post('follow_up'),
                            'user_stamp'=>  $this->session->userdata('user_id'),
                            'date_stamp'=>  date('Y-m-d')
                        );
        $this->db->insert('activity_transaction',$value); 
		
		$value2['point'] = "point + 1";
		$this->db->where('id',$cust);
		$this->db->update('customers',$value2);
    }
    
    function getUpdate(){
        $this->db->where('customer_name',$this->input->post('customer'));
        $Q=$this->db->get('customers');
        $row=$Q->row_array();
        $cust=$row['id'];
        $pic =$row['rev'];
        
        $hour = $this->input->post('hour');
        $minute = $this->input->post('minute');
        $second = $this->input->post('second');
        $time = $hour.':'.$minute.':'.$second;
        
        $value = array  (
                            'activity_desc'     =>  $this->input->post('desc'),
                            'activity_id'       =>  $this->input->post('type'),
                            'customer_id'       =>  $cust,
                            'pic'               =>  $pic,
                            'activity_time'     =>  $time,
                            'activity_date'     =>  $this->input->post('date'),
                            'activity_location' =>  $this->input->post('location'),
                            'activity_people'   =>  $this->input->post('people'),
                            'activity_objective'=>  $this->input->post('objective'),
                            'activity_result'   =>  $this->input->post('result'),
                            'activity_follow_up'=>  $this->input->post('follow_up'),
                            'user_stamp'=>  $this->session->userdata('user_id'),
                            'date_stamp'=>  date('Y-m-d')
                        );
        $this->db->where('id',$this->input->post('id'));                 
        $this->db->update('activity_transaction',$value);                
    }
    
    function getRemove($id){
        $this->db->where('id',$id);
        $this->db->delete('activity_transaction');
    }
    
    function getTypeCek(){
        $sql ="SELECT * FROM activity ";
		$sql.="WHERE activity_type = '".$this->input->post('name')."' LIMIT 1";
        $query=$this->db->query($sql);
		return $query->row_array();
    }
    
    function getTypeSave(){
        $value = array  (
                            'activity_type' =>  $this->input->post('name'),
                            'target'        =>  $this->input->post('target'),
                            'user_stamp'    =>  $this->session->userdata('user_id'),
                            'date_stamp'    =>  date('Y-m-d')
                        );               
        $this->db->insert('activity',$value); 
    }
    
    function getTypeUpdate(){
        $value = array  (
                            'activity_type' =>  $this->input->post('name'),
                            'user_stamp'    =>  $this->session->userdata('user_id'),
                            'date_stamp'    =>  date('Y-m-d')
                        );
        $this->db->where('id',$this->input->post('id'));                 
        $this->db->update('activity',$value); 
    }
    
    function getTypeRemove(){
        $this->db->where('id',$this->input->post('id'));
        $this->db->delete('activity');
    }
    
    function getAllTarget(){
        $date=date('m');
        if($date=='01'):
            $month = 12;
            $year  = date('Y')-1;
        else:
            $month = date('m')-1;
            $year  = date('Y');
        endif;
        $day   = days_in_month($month,$year);
        $date=  $year.'-'.$month.'-'.$day;
        
        $sql = " SELECT *,at.id,at.activity_id,WEEK(at.activity_date) as week,DATE_FORMAT('".$date."','%u') as week2, ";
        $sql.= " (SELECT COUNT(at2.activity_id) FROM activity_transaction at2 WHERE at2.pic=at.pic AND WEEK(at2.activity_date)=WEEK(at.activity_date) AND YEAR(at2.activity_date)=YEAR(at.activity_date) AND at2.activity_id=1 )as call1,";
        $sql.= " (SELECT COUNT(at2.activity_id) FROM activity_transaction at2 WHERE at2.pic=at.pic AND WEEK(at2.activity_date)=WEEK(at.activity_date) AND YEAR(at2.activity_date)=YEAR(at.activity_date) AND at2.activity_id=3 )as presentation,";
        $sql.= " (SELECT COUNT(at2.activity_id) FROM activity_transaction at2 WHERE at2.pic=at.pic AND WEEK(at2.activity_date)=WEEK(at.activity_date) AND YEAR(at2.activity_date)=YEAR(at.activity_date) AND at2.activity_id=4 )as meeting,";
        $sql.= " MONTHNAME(at.activity_date) as month,";
        $sql.= " (at.activity_date) as month1,YEAR(at.activity_date) as year,at.pic ";
        $sql.= " FROM activity_transaction at  ";
        $sql.= " LEFT JOIN customers c  ON c.id=at.customer_id ";
        $sql.= " LEFT JOIN users u  ON u.username=at.pic ";
        $sql.= " WHERE MONTH(at.activity_date)='".date('m')."' ";
        if(($this->session->userdata('user_pos')== $this->config->item('ta'))):
            $sql.= " AND at.pic='".$this->session->userdata('user_id')."'";
        elseif(($this->session->userdata('user_pos')== $this->config->item('se')) && ($this->session->userdata('user_group')== $this->config->item('user')) ):    
            $sql.= " AND at.pic='".$this->session->userdata('user_ta')."'";
        elseif(($this->session->userdata('user_pos')== $this->config->item('se')) && ($this->session->userdata('user_group')== $this->config->item('user_dna')) ):    
            $sql.= " AND at.pic='".$this->session->userdata('user_id')."'";    
        elseif(($this->session->userdata('user_group')== $this->config->item('admin')) ):    
            $sql.= " AND u.group_id=".$this->config->item('user');     
        elseif(($this->session->userdata('user_group')== $this->config->item('admin_dna')) ):    
            $sql.= " AND u.group_id=".$this->config->item('user_dna');    
        else:
        $sql.= " ";
        endif;
		$sql.=" GROUP BY MONTH(at.activity_date),WEEK(at.activity_date),YEAR(at.activity_date),at.pic ";
        $sql.=" ORDER BY MONTH(at.activity_date),WEEK(at.activity_date) DESC ";
        
		$query=$this->db->query($sql);
		return $query->result_array();
	}
    
    function getWeekTarget($week,$month,$year,$user){
        $sql =" SELECT * ,COUNT(at.activity_id) as target,at.id,WEEK(at.activity_date) as week,MONTHNAME(at.activity_date) as month,YEAR(at.activity_date) as year ";
        $sql.=" FROM activity_transaction at  ";
        $sql.=" LEFT JOIN customers c  ON c.id=at.customer_id ";
        $sql.=" WHERE WEEK(at.activity_date)='".$week."' ";
        $sql.=" AND   MONTH(at.activity_date)='".$month."' ";
        $sql.=" AND   YEAR(at.activity_date)='".$year."' ";
		$sql.=" AND at.pic='".$user."' ";
		$query=$this->db->query($sql);
		return $query->result_array();
	}
    
   
    
    function getAllThisTarget($week,$year='',$pic='',$group=''){
        $sql =" SELECT * ,ath.id,WEEK(ath.activity_date) as week,MONTHNAME(ath.activity_date) as month,YEAR(ath.activity_date) as year ";
        $sql.=" FROM activity_transaction ath  ";
        $sql.=" LEFT JOIN customers c  ON c.id=ath.customer_id ";
        $sql.=" LEFT JOIN users u  ON u.username=ath.pic ";
        $sql.=" WHERE  ath.activity_date<>'0000-00-00' AND ath.activity_id=3 ";
        $sql.=" AND  WEEK(ath.activity_date) ='".$week."'";
            $sql.=" AND  YEAR(ath.activity_date) ='".$year."'";    
        if($pic<>''):
            $sql.=" AND  ath.pic ='".$pic."'";   
        endif;
        if($group<>''):
            $sql.=" AND  u.group_id ='".$group."'";   
        endif;
		$query=$this->db->query($sql);
		return $query->result_array();
	}
    
    function getAllThisTargetDetails(){
        $sql ="SELECT *,atd.id FROM activity_transaction atd ";
		$sql.="ORDER BY atd.activity_id ASC ";
 	    $query=$this->db->query($sql);
		return $query->result_array();
    }
	
	function getAllSalesFails(){
        $sql =" SELECT *,sf.id FROM sales_fails sf";
		$sql.=" LEFT JOIN customers c  ON c.id=sf.cust_id ";
		$sql.=" LEFT JOIN products p  ON p.id=sf.prod_id ";
		$sql.=" ORDER BY sf.date_posting DESC ";
 	    $query=$this->db->query($sql);
		return $query->result_array();
    }
    
    function getAllDataTargetDetails($week,$year,$user){
        $sql =" SELECT *,at.id FROM activity_transaction at ";
        $sql.=" LEFT JOIN customers c  ON c.id=at.customer_id ";
        $sql.=" WHERE WEEK(at.activity_date)='".$week."' ";
        $sql.=" AND YEAR(at.activity_date)='".$year."' ";
        $sql.=" AND at.pic='".$user."' ";
		$sql.=" ORDER BY at.activity_id ASC ";
 	    $query=$this->db->query($sql);
		return $query->result_array();
    }
    
    function getDataTarget($id){
		$sql ="SELECT *,ath.id FROM activity_target_head ath ";
        $sql.="LEFT JOIN customers c ON c.id=ath.customer_id ";
        $sql.="WHERE ath.id = '".$id."' LIMIT 1";
		$query=$this->db->query($sql);
		return $query->row_array();
	}
    
    function getTargetCek(){
        if(($this->input->post('date1')<>"") || ($this->input->post('customer1')<>"") ):
            $date=$this->input->post('date1');
            $cust=$this->input->post('customer1');
        else:
            $date=$this->input->post('date2');
            $cust=$this->input->post('customer2');
        endif;
            
        $sql ="SELECT * FROM activity_transaction at ";
		$sql.="WHERE at.activity_date = '".$date."' AND ";
        $sql.=" at.customer_id  = ".$cust."  ";
        $query=$this->db->query($sql);
		return $query->row_array();
    }
    
    function getFailedCek(){
            
        $sql ="SELECT * FROM sales_fails ";
		$sql.="WHERE date_in = '".$this->input->post('datein')."' AND ";
        $sql.=" cust_id  = '".$this->input->post('customer')."' AND ";
        $sql.=" prod_id = ".$this->input->post('product')." LIMIT 1";
        $query=$this->db->query($sql);
		return $query->row_array();
    }
    
    function getFailedSave(){
        $date_in=$this->input->post('datein');
        $date_out=$this->input->post('dateout');
        $cust=$this->input->post('customer');
        $prod=$this->input->post('product');
        $note=$this->input->post('desc');
        $status=$this->input->post('status');
        
        $total=count($cust);
        
        for($i=0;$i<$total;$i++):
            if(($cust[$i]<>"") && ($date_in[$i]<>"") && ($date_out[$i]<>"") ):
                $value = array  (
                                    'date_in'       =>  $date_in[$i],
                                    'date_out'      =>  $date_out[$i],
                                    'cust_id'       =>  $cust[$i],
                                    'prod_id'       =>  $prod[$i],
                                    'note'          =>  $note[$i],
                                    'status'        =>  $status[$i],
                                    'date_posting'  =>  date('Y-m-d'),
                                    'user_stamp'    =>  $this->session->userdata('user_id'),
                                    'date_stamp'    =>  date('Y-m-d')
                                );               
                $this->db->insert('sales_fails',$value);
            endif;
        endfor;        
    }
    
    function getTargetSave(){
        if(($this->input->post('date1')<>"") || ($this->input->post('customer1')<>"") ):
            $date=$this->input->post('date1');
			$time=$this->input->post('time1');
            $cust=$this->input->post('customer1');
        else:
            $date=$this->input->post('date2');
			$time=$this->input->post('time2');
            $cust=$this->input->post('customer2');
        endif;
        
        $totaldate=count($date);
        $totalcust=count($cust);
        
        for($i=0;$i<$totaldate;$i++):
          if(($date[$i]<>"") && ($cust[$i]<>"")):
            $this->db->where('id',$cust[$i]);
            $Q=$this->db->get('customers');
            $rown=$Q->row_array();
            
            if(COUNT($rown)>0):
                if($this->session->userdata('user_group')==$this->config->item('admin')):
                    $rev=$rown['rev'];
                elseif($this->session->userdata('user_group')==$this->config->item('admin_dna')):
                    $rev=$rown['rev2'];
                elseif($this->session->userdata('user_group')==$this->config->item('user')):
                    //$rev=$rown['rev'];
					$rev=$this->session->userdata('user_id');
                elseif($this->session->userdata('user_group')==$this->config->item('user_dna')):
                    $rev=$this->session->userdata('user_id');    
                endif;                        
            else:
                $rev = $this->session->userdata('user_id');
            endif;
                
            $value = array  (
                                'activity_date' =>  $date[$i],
								'activity_time' =>  $time[$i],
                                'customer_id'   =>  $cust[$i],
                                'activity_id'   =>  3,
                                'pic'           =>  $rev,
                                'user_stamp'    =>  $this->session->userdata('user_id'),
                                'date_stamp'    =>  date('Y-m-d')
                            );               
            $this->db->insert('activity_transaction',$value);
         endif;
        endfor;    
    }
    
    function getTargetUpdate(){
        
        if(($this->input->post('date1')<>"") || ($this->input->post('customer1')<>"") ):
            $date=$this->input->post('date1');
            $cust=$this->input->post('customer1');
        else:
            $date=$this->input->post('date2');
            $cust=$this->input->post('customer2');
        endif;
        
        
        $this->db->where('id',$cust);
        $Q=$this->db->get('customers');
        $rown=$Q->row_array();
        
        if(COUNT($rown)>0):
            if($this->session->userdata('user_group')==$this->config->item('admin')):
                $rev=$rown['rev'];
            elseif($this->session->userdata('user_group')==$this->config->item('admin_dna')):
                $rev=$rown['rev2'];
            elseif($this->session->userdata('user_group')==$this->config->item('user')):
                $rev=$rown['rev'];
            elseif($this->session->userdata('user_group')==$this->config->item('user_dna')):
                $rev=$this->session->userdata('user_id');    
            endif; 
        else:
            $rev="Uknown";
        endif;
        
        $value = array  (
                            'activity_date'   =>  $date,
                            'customer_id'     =>  $cust,
                            'pic'             =>  $rev,
                            'activity_id'     =>  3,
                            'user_stamp'      =>  $this->session->userdata('user_id'),
                            'date_stamp'      =>  date('Y-m-d')
                        ); 
        $this->db->where('id',$this->input->post('id'));                              
        $this->db->update('activity_transaction',$value);
    }
    
    function getActivityMonth(){
        $sql = "  SELECT a.id,a.activity_type ";
        $sql.=" FROM activity a ";
        $sql.=" WHERE a.id = 1 OR  ";
        $sql.=" a.id  = 3 OR ";
        $sql.=" a.id  = 4 ";
        $query=$this->db->query($sql);
		return $query->result_array();
    }
    
    function getTargetRemove($id){
        $this->db->where('id',$id);
        $this->db->delete('activity_transaction');    
    }
    
    function getFailedRemove($id){
        $this->db->where('id',$id);
        $this->db->delete('sales_fails');    
    }
	
	function getLastJob(){
        $sql ="SELECT *,j.id,j.pic,((p.invest + (p.invest * j.ppn / 100) - j.discount) ) as total FROM jobs j ";
		$sql.="LEFT JOIN products p ON p.id=j.product_id ";
		$sql.="LEFT JOIN customers c ON c.id=j.customer_id ";
        $sql.="WHERE WEEK(j.date_stamp)=WEEK(curdate())";
		$sql.="AND j.pic='".$this->session->userdata('user_id')."'";
        $query=$this->db->query($sql);
		return $query->result_array();
    }
    
}